package com.minisoft.ydac;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;

import java.io.File;
import java.util.*;

public class ExcelMatchCustomerOpportunityId {

    public static void main(String[] args) {
        // 读取两个Excel文件
        String saleTeamId = "1001242";//1000114,1001242,1001272
        File excelFile1 = FileUtil.file("D:\\temp\\aivoice_customer_data3.xlsx");
        File excelFile2 = FileUtil.file(StrUtil.format("D:\\temp\\daasResult-{}.xlsx",saleTeamId));

        // 读取Excel数据
        ExcelReader reader1 = ExcelUtil.getReader(excelFile1);
        ExcelReader reader2 = ExcelUtil.getReader(excelFile2);

        // 读取表格1的数据
        List<List<Object>> data1 = reader1.read(1); // 从第二行开始读取数据
        // 读取表格2的数据
        List<List<Object>> data2 = reader2.read(1); // 从第二行开始读取数据

        // 创建一个映射来存储表格2的数据
        Map<String, List<List<Object>>> mobileToOpportunityIdMap = new HashMap<>();
        for (List<Object> row : data2) {
            String mobile = (String) row.get(0);
            String opportunityId = (String) row.get(1);
            if (StrUtil.isNotBlank(mobile) && mobile.length() == 11) {
                // 使用前3位和后4位作为键
                String key = saleTeamId + mobile.substring(0, 3) + mobile.substring(mobile.length() - 4);
                mobileToOpportunityIdMap.computeIfAbsent(key, k -> new ArrayList<>()).add(row);
            }
        }

        // 匹配并填充表格1
        for (List<Object> row : data1) {
            String mobile = row.get(4).toString();
            String saleTeamId2 = row.get(8).toString();
            if (Objects.equals(saleTeamId,saleTeamId2) && mobile.length() == 11) {
                // 使用前3位和后4位作为键
                String key = saleTeamId + mobile.substring(0, 3) + mobile.substring(mobile.length() - 4);
                List<List<Object>> opportunityIds = mobileToOpportunityIdMap.get(key);
                if (CollUtil.isNotEmpty(opportunityIds)) {
                    if (opportunityIds.size() == 1) {
                        row.set(2, opportunityIds.get(0).get(1));
                    } else {
                        String opportunityId = null;
                        List<String> opportunityIdList = new ArrayList<>();
                        for (List<Object> customer : opportunityIds) {
                            opportunityIdList.add((String) customer.get(1));
                            //优先姓名相同，顾问相同
                            if (Objects.equals(customer.get(2), row.get(3))) {
                                opportunityId = (String) customer.get(1);
                            } else if (Objects.equals(customer.get(3), row.get(6)) && opportunityId == null) {
                                opportunityId = (String) customer.get(1);
                            }
                        }
                        if (opportunityId != null) {
                            row.set(2, opportunityId);
                        } else {
                            row.set(2, CollUtil.join(opportunityIdList,","));
                        }
                    }
                }
            }
        }

        // 写入原来的Excel文件
        ExcelWriter writer = ExcelUtil.getWriter(excelFile1);
        writer.setCurrentRow(1);
        writer.write(data1, false); // 第二个参数设置为true表示追加写入
        writer.close();

        System.out.println("处理完成！");
    }
}